import requests
import sqlite3
import pandas as pd
import time
import akshare as ak
import datetime
from bs4 import BeautifulSoup
from comm.tsapis import ts,logger,Tsapi
TimeOut = 5
tb_nm = 'top10_holder'

def tableCheck(conn):
    curs = conn.cursor()
    sql = "SELECT name num FROM sqlite_master WHERE type='table' AND name='{0}'".format(tb_nm)
    curs.execute(sql)
    isexists = curs.fetchall()
    if len(isexists) > 0:
        curs.close()
    else:
        sql = """CREATE TABLE top10_holder (
         stock_cd TEXT,
         rpt_dt TEXT,
         holder_nm TEXT,
         hold_vol REAL,
         hold_rate REAL,
         chng_typ TEXT,
         chag_vol REAL
         )"""
        curs.execute(sql)
        curs.close()


def get_last_rpt_dt(conn):
    curs = conn.cursor()
    sql = "SELECT stock_cd, max(rpt_dt) rpt_dt FROM {0} group by stock_cd".format(tb_nm)
    curs.execute(sql)
    dates = curs.fetchall()
    if len(dates) > 0:
        return dict(dates)
    else:
        return {'1000': '2020-01-01'}


def get_chg_type(row):
    if row['chag_vol']>0:
        return '增加'
    elif  row['chag_vol']<0:
        return '减少'
    else:
        return row['chng_typ']

def top10gd():
    ts=Tsapi()
    #if 'stocks' not in dir():
    stocks=ts.get_stock_list()
        # 查询当前所有正常上市交易的股票列表
    tableCheck(ts.conn.get_conn_refresh())
    stock_max_rpt_dt = get_last_rpt_dt(ts.conn.get_conn_refresh())
    # stlist = list(stocks['stock_cd'])
    total = len(stocks)
    today = datetime.datetime.today()
    last_q_end = (today + pd.tseries.offsets.DateOffset(months=-((today.month - 1) % 3),
                                                        days=1 - today.day - 1)).strftime('%Y-%m-%d')  # 当季第一天
    i = 0
    for js in stocks.to_dict('records'):
        i = i + 1
        code=js['stock_cd']
        max_rpt_dt = stock_max_rpt_dt.get(code, js['beg_dt'] if js['beg_dt']>='20210630' else '20210630')
        if max_rpt_dt < last_q_end:
            start_dt=max_rpt_dt
            end_dt=last_q_end
            dates = list(pd.date_range(start=start_dt,end=end_dt,freq='Q').astype(str))
            if start_dt in dates:
                dates.remove(start_dt)
            if(i%100==0):
                logger.info(f"正在获取第{i}个,共：{total} {js['stock_cd']} {dates} ")
            # if max_rpt_dt < last_q_end:
            #     df = gpdmgd_top10(code)
            #     df = df[df['rpt_dt'] > max_rpt_dt]
            #     df.to_sql(tb_nm, ts.conn.get_conn_refresh(), if_exists='append', index=False)
            #     time.sleep(3)  # sleep
            for dt in dates:
                # print(dt.replace('-',''))
                # logger.info(f"{js['symbol']} dt:{dt.replace('-','')}")
                try:
                    # logger.info(js)
                    df = ak.stock_gdfx_top_10_em(symbol=js['symbol'], date=dt.replace('-','')) #'股东名称': 'holder_nm',
                except Exception as e:
                    logger.error(f'error:{str(e)} for {code} with symbol')
                    if str(e).startswith('Length mismatch'):
                        break
                    else:
                        raise e
                renames = { '持股数': 'hold_vol','股东名称': 'holder_nm',
                                   '占总股本持股比例': 'hold_rate', '增减': 'chng_typ', '变动比率': 'chag_vol'}
                # print(df)
                df.rename(columns=renames, inplace=True)
                # df['hold_vol'] = df['hold_vol'].astype(float)
                df['hold_rate'] = df['hold_rate'].astype(float)
                df['chag_vol'] = df['hold_vol']*df['chag_vol']#.astype(float) #.apply(to_float)
                df['chng_typ']=df.apply(get_chg_type,axis=1)
                df = df[list(renames.values())]
                # print(df)
                if df.shape[0]>1:
                    df['rpt_dt']=dt
                    df['stock_cd']= code
                    df.to_sql(tb_nm, ts.conn.get_conn_refresh(), if_exists='append', index=False)
                    #time.sleep(0.5)  # sleep
                else:
                    break

    # url = "http://cwzx.shdjt.com/gpdmgd.asp?gpdm=" + str(stock_code)
    # #logger.info(url)
    # headers = {
    #     'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36'};
    # result = requests.session().get(url, headers=headers, timeout=TimeOut)  # requests.get(url);
    # soup = BeautifulSoup(result.text, "html.parser")
    # tbs1 = soup.find_all("table")
    # arr = []
    # for tb1 in tbs1:
    #     tbs2 = tb1.find_all("table")
    #     for tb2 in tbs2:
    #         tbs3 = tb2.find_all("table")
    #         for tb3 in tbs3:
    #             trs = tb3.find_all('tr')
    #             for tr in trs:
    #                 tds = tr.find_all('td')
    #                 if len(tds) > 14 and ('十大股东' in tds[6].text or '股东类型' in tds[6].text):
    #                     tp = []
    #                     for td in tds:
    #                         tp.append(td.text)
    #                     arr.append(tp)
    # if len(arr) > 1:
    #     df = pd.DataFrame(arr[1:], columns=arr[0])
    #     renames = {'股票代码': 'stock_cd', '更新日期': 'rpt_dt', '股东名称': 'holder_nm', '持股数(万)': 'hold_vol',
    #                '比例': 'hold_rate', '增减仓': 'chng_typ', '数量': 'chag_vol'}
    #     df.rename(columns=renames, inplace=True)
    #     df['hold_vol'] = df['hold_vol'].astype(float)
    #     df['hold_rate'] = df['hold_rate'].astype(float)
    #     df['chag_vol'] = df['chag_vol'].apply(to_float)
    #     df = df[list(renames.values())]
    #     df['rpt_dt'] = pd.to_datetime(df['rpt_dt'])
    #     df['rpt_dt'] = df['rpt_dt'].apply(lambda x: str(x)[0:10])
    #     # df.drop(columns='序',inplace=True,errors='ignore')
    #     return df
    # else:
    #     return pd.DataFrame()


if __name__ == '__main__':
   top10gd()